package com.panopset.db;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

public class DBmgr {

    public Connection getConn() {
        return ConnectionManager.getConnection();
    }

    public long execUpdate(final SqlSpec dc, final String idFieldName) {
        String msql = dc.getSql();
        for (Entry<Integer, Object> es : dc.getParams().entrySet()) {
            StringWriter sw = new StringWriter();
            if (es.getValue() instanceof Integer) {
                sw.append(es.getValue().toString());
            } else {
                sw.append("'");
                sw.append(es.getValue().toString());
                sw.append("'");
            }
            msql = msql.replaceFirst("\\?", sw.toString());
        }
        dc.dumpSql();
        try (Connection conn = getConn();
                PreparedStatement stmt = conn.prepareStatement(msql,
                        new String[] {idFieldName })) {
            stmt.executeUpdate(msql, Statement.RETURN_GENERATED_KEYS);
            long rtn = -1;
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs != null && rs.next()) {
                rtn = rs.getLong(1);
            }
            stmt.close();
            return rtn;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void execNoParamSql(String sql) {
        SqlSpec dc = new SqlSpec();
        dc.setSql(sql);
        exec(dc);
    }

    public void exec(SqlSpec dc) {
        dc.dumpSql();
        resolveParams(dc);
        try (Connection conn = getConn();
                PreparedStatement stmt = conn.prepareStatement(dc.getSql())) {
            addParamsToStatement(stmt, dc);
            stmt.execute();
            stmt.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public List<Map<String, Object>> qry(final String sql) {
        SqlSpec dc = new SqlSpec();
        dc.setSql(sql);
        return qry(dc);
    }

    public List<Map<String, Object>> qry(final SqlSpec dc) {
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

        if (!resolveParams(dc)) {
            return result;
        }
        try (Connection conn = getConn();
                PreparedStatement stmt = conn.prepareStatement(dc.getSql())) {
            dc.dumpSql();
            addParamsToStatement(stmt, dc);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();
            List<String> columns = new ArrayList<String>();
            if (rs == null) {
                return result;
            }
            ResultSetMetaData rsMetaData = rs.getMetaData();
            if (rsMetaData == null) {
                return result;
            }
            int columnCount = rsMetaData.getColumnCount();
            if (columnCount < 1) {
                return result;
            }
            for (int i = 0; i < columnCount; i++) {
                columns.add(rsMetaData.getColumnLabel(i + 1));
            }
            while (rs.next()) {
                Map<String, Object> row = new HashMap<String, Object>();
                for (String key : columns) {
                    row.put(key, rs.getObject(key));
                }
                result.add(row);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    private void addParamsToStatement(PreparedStatement stmt, SqlSpec dc)
            throws SQLException {
        for (Entry<Integer, Object> es : dc.getParams().entrySet()) {
            Object v = es.getValue();
            if (v instanceof Long) {
                stmt.setLong(es.getKey(), (Long) v);
            } else if (v instanceof Integer) {
                stmt.setLong(es.getKey(), (Integer) v);
            } else if (v instanceof Timestamp) {
                stmt.setTimestamp(es.getKey(), (Timestamp) v);
            } else if (v instanceof String) {
                stmt.setString(es.getKey(), (String) v);
            } else {
                throw new RuntimeException(v.getClass().getCanonicalName());
            }
        }
    }

    private boolean resolveParams(SqlSpec dc) {
        for (Entry<Integer, Object> es : dc.getParams().entrySet()) {
            if (es.getValue() instanceof ArrayList) {
                for (Object lp : ((ArrayList<?>) es.getValue())) {
                    if (!addStmtParam(dc, es.getKey(), lp)) {
                        return false;
                    }
                }
            } else {
                if (!addStmtParam(dc, es.getKey(), es.getValue())) {
                    return false;
                }
            }
        }
        return true;
    }

    private boolean addStmtParam(SqlSpec dc, int i, Object value) {
        if (value instanceof DataObject) {
            DataObject row = (DataObject) value;
            if (row.getId() == DataObject.NOID) {
                long id = row.getTableDescription().getIdByIndexes(row);
                if (id == DataObject.NOID) {
                    return false;
                }
                row.setId(id);
            }
            dc.setObject(i, row.getId());
        } else {
            dc.setObject(i, value);
        }
        return true;
    }
}
